/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package db;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Time;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import vanScheduling.Service;

/**
 *
 * @author Dan
 */
public class ServiceDAO extends DAOTest {

    public static List<Service> getServices()
    {
        Connection con = getConnection();
        ArrayList<Service> services = new ArrayList<Service>();
        try {
            CallableStatement cs = con.prepareCall("{call stp_selAllServices(?)}");
            cs.setInt(1, 8); //hard coded now, needs to be current semester
            ResultSet rs = cs.executeQuery();

            while (rs.next())
            {
                int id = rs.getInt("activityid");
                String name = rs.getString("activity-name");
                Time startTime = rs.getTime("begin_time");
                Time endTime = rs.getTime("end_time");
                int driveTime = rs.getInt("travel_time");
                String location = rs.getString("location");
                boolean sun = rs.getBoolean("sun");
                boolean mon = rs.getBoolean("mon");
                boolean tue = rs.getBoolean("tue");
                boolean wed = rs.getBoolean("wed");
                boolean thurs = rs.getBoolean("thurs");
                boolean fri = rs.getBoolean("fri");
                boolean sat = rs.getBoolean("sat");
                Date startDate = rs.getDate("begin_date");
                Date endDate = rs.getDate("end_date");
                services.add(new Service(id, name, startTime, endTime, driveTime, location,
                        sun, mon, tue, wed, thurs, fri, sat, startDate, endDate));
            }

            cs.close();
            con.close();
        }
        catch (SQLException e) {System.err.println(e.getMessage());e.printStackTrace();}
        return services;
    }

   
    public static Service getServiceByID(int serviceID) {
        Connection con = getConnection();
        Service returnService = null;

        try {
            CallableStatement cs = con.prepareCall("{call dbo.stp_selServiceByID(?, ?)}");
            cs.setInt(1, 8); //hard coded now, needs to be current semester
            cs.setInt(2, serviceID);

            ResultSet rs = cs.executeQuery();

            while (rs.next())
            {
                int id = rs.getInt("activityid");
                String name = rs.getString("activity-name");
                Time startTime = rs.getTime("begin_time");
                Time endTime = rs.getTime("end_time");
                int driveTime = rs.getInt("travel_time");
                String location = rs.getString("location");
                boolean sun = rs.getBoolean("sun");
                boolean mon = rs.getBoolean("mon");
                boolean tue = rs.getBoolean("tue");
                boolean wed = rs.getBoolean("wed");
                boolean thurs = rs.getBoolean("thurs");
                boolean fri = rs.getBoolean("fri");
                boolean sat = rs.getBoolean("sat");
                Date startDate = rs.getDate("begin_date");
                Date endDate = rs.getDate("end_date");
                returnService = new Service(id, name, startTime, endTime, driveTime, location,
                        sun, mon, tue, wed, thurs, fri, sat, startDate, endDate);
            }

            cs.close();
            con.close();
        }
        catch (SQLException e) {e.printStackTrace();}
        return returnService;
    }

    /*
    public synchronized static boolean saveDriver(Driver driver)
    {
        Connection con = getConnection();
        boolean result = false;

        try {
            CallableStatement cs = con.prepareCall("{call dbo.stp_updDriver(?, ?, ?, ?)}");
            cs.setInt(1, driver.getId());
            cs.setString(2, driver.getName());
            cs.setString(3, driver.getEmail());
            cs.setString(4, driver.getPhoneNumber());
            int added = cs.executeUpdate();

            result = added > 0;

            cs.close();
            con.close();
        }
        catch (SQLException e) {System.err.println(e.getMessage());e.printStackTrace();}

        return result;
    }

     */

    public static ArrayList<Service> getServicesByDay() {
        
        String day = Service.getDayStatic();
        
        Connection con = getConnection("BUNSEN");
        ArrayList<Service> services = new ArrayList<Service>();

        try {
            Statement stmt = con.createStatement();

            String select = "SELECT "
                    + "activityid, "
                    + "[activity-name], "
                    + "begin_date,"
                    + "end_date, "
                    + "begin_time, "
                    + "end_time, "
                    + "address,"
                    + "travel_time "
                    + "FROM BUNSEN.service.dbo.COMSERV_ACTIVITY "
                    + "WHERE "
                    + "maxstu > 0 "
                    + "AND travel_time > 0 "
                    + "AND semester_yearid = 8"
                    + "AND "+day+" = 1";

            ResultSet rs = stmt.executeQuery(select);
            while (rs.next()) {
                int id = rs.getInt("activityid");
                String name = rs.getString("activity-name");
                Time startTime = rs.getTime("begin_time");
                Time endTime = rs.getTime("end_time");
                int driveTime = rs.getInt("travel_time");
                String location = rs.getString("location");
                Date startDate = rs.getDate("begin_date");
                Date endDate = rs.getDate("end_date");
                services.add(new Service(id, name, startTime, endTime,
                        driveTime, location, startDate, endDate));
                
            }
            //close
            stmt.close();
            con.close();
        }
        catch (SQLException e) {System.err.println(e.getMessage());}
        catch (Exception e) {System.err.println(e.getMessage());}

        return services;
    }
    String select = "DECLARE @SQLSTATEMENT VARCHAR(1000) "
                    + "DECLARE @DAYCLAUSE VARCHAR(100) "
                    + "SET @SQLSTATEMENT = 'SELECT "
                    + "CA.activityid, CA.[activity-name], CA.begin_date,"
                    + "CA.end_date, CA.begin_time, CA.end_time, CA.address,"
                    + "CA.travel_time FROM BUNSEN.service.dbo.COMSERV_ACTIVITY CA "
                    + "WHERE CA.maxstu > 0 AND CA.travel_time > 0 AND "
                    + "((CA.begin_date < GETDATE() AND CA.end_date > GETDATE()) "
                    + "OR (CA.begin_date > GETDATE())) AND ' "
                    + "IF @DAYCODE = 'mon' SET @DAYCLAUSE = 'CA.mon = 1' "
                    + "IF @DAYCODE = 'tue' SET @DAYCLAUSE = 'CA.tue = 1' "
                    + "IF @DAYCODE = 'wed' SET @DAYCLAUSE = 'CA.wed = 1' "
                    + "IF @DAYCODE = 'thurs' SET @DAYCLAUSE = 'CA.thurs = 1' "
                    + "IF @DAYCODE = 'fri' SET @DAYCLAUSE = 'CA.fri = 1' "
                    + "IF @DAYCODE = 'sat' SET @DAYCLAUSE = 'CA.sat = 1' "
                    + "IF @DAYCODE = 'sun' SET @DAYCLAUSE = 'CA.sun = 1' "
                    + "SET @SQLSTATEMENT = @SQLSTATEMENT + @DAYCLAUSE "
                    + "EXEC(@SQLSTATEMENT)";
}
